﻿Option Strict On
Option Explicit On

Imports CObject
Imports ConnectionServer
Imports System.Data.SqlClient
Imports Objects.Utility

Namespace CMT

    Public Class EventAttendee
        Inherits CObject.CObject

        Public Sub New()
            MyBase.New()
        End Sub

        Public Sub New(ByVal piEventAttendeeID As Integer)
            MyBase.New(piEventAttendeeID)
        End Sub

        Public Overrides Function Clone() As CObject.CObject
            Return New EventAttendee
        End Function

        Public Overrides ReadOnly Property Key As String
            Get
                Return "EventAttendeeID"
            End Get
        End Property

        Protected Overrides Function ProcedureSigniture(ByVal peCommandType As CObject.CommandType) As String
            Select Case peCommandType
                Case CommandType.SQLSelect
                    Return "EXEC CMT_GetEventAttendeesDetails {EventID} "
                Case CommandType.SQLDelete
                    Return "EXEC dbo.CMT_EventAttendeeDelete {EventAttendeeID}"
                Case Else
                    Throw New SystemException("Invalid Command Type")
            End Select
        End Function

        Public Overrides ReadOnly Property SourceDataBase As ConnectionServer.Connection.SourceDataBase
            Get
                Return Connection.SourceDataBase.CMT
            End Get
        End Property

#Region "Properties"

        Public Property EventAttendeeID As Integer
            Get
                Return CastToInteger(GetProperty("EventAttendeeID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("EventAttendeeID", value)
            End Set
        End Property

        Public Property EventID As Integer
            Get
                Return CastToInteger(GetProperty("EventID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("EventID", value)
            End Set
        End Property

        Public Property FirstName As String
            Get
                Return CastToString(GetProperty("FirstName"))
            End Get
            Set(ByVal value As String)
                SetProperty("FirstName", value)
            End Set
        End Property

        Public Property LastName As String
            Get
                Return CastToString(GetProperty("LastName"))
            End Get
            Set(ByVal value As String)
                SetProperty("LastName", value)
            End Set
        End Property

        Public Property Salutation As String
            Get
                Return CastToString(GetProperty("Salutation"))
            End Get
            Set(ByVal value As String)
                SetProperty("Salutation", value)
            End Set
        End Property

        Public Property PreferredFormOfAddress As String
            Get
                Return CastToString(GetProperty("PrefFormOfAddress"))
            End Get
            Set(ByVal value As String)
                SetProperty("PrefFormOfAddress", value)
            End Set
        End Property

        Public Property EmailAddress As String
            Get
                Return CastToString(GetProperty("EmailAddress"))
            End Get
            Set(ByVal value As String)
                SetProperty("EmailAddress", value)
            End Set
        End Property

        Public Property Office As String
            Get
                Return CastToString(GetProperty("Office"))
            End Get
            Set(ByVal value As String)
                SetProperty("Office", value)
            End Set
        End Property

        Public Property City As String
            Get
                Return CastToString(GetProperty("City"))
            End Get
            Set(ByVal value As String)
                SetProperty("City", value)
            End Set
        End Property

        Public Property RepresentingCountry As String
            Get
                Return CastToString(GetProperty("RepresentingCountry"))
            End Get
            Set(ByVal value As String)
                SetProperty("RepresentingCountry", value)
            End Set
        End Property

        Public Property DirectDial As String
            Get
                Return CastToString(GetProperty("DirectDial"))
            End Get
            Set(ByVal value As String)
                SetProperty("DirectDial", value)
            End Set
        End Property

        Public Property MobileNumber As Double
            Get
                Return CastToDouble(GetProperty("MobileNumber"))
            End Get
            Set(ByVal value As Double)
                SetProperty("MobileNUmber", value)
            End Set
        End Property

        Public Property AssitantName As String
            Get
                Return CastToString(GetProperty("AssitantName"))
            End Get
            Set(ByVal value As String)
                SetProperty("AssitantName", value)
            End Set
        End Property

        Public Property AssistantNumber As Double
            Get
                Return CastToDouble(GetProperty("AssistantNumber"))
            End Get
            Set(ByVal value As Double)
                SetProperty("AssistantNumber", value)
            End Set
        End Property

        Public Property AssitantEmail As String
            Get
                Return CastToString(GetProperty("AssitantEmail"))
            End Get
            Set(ByVal value As String)
                SetProperty("AssitantEmail", value)
            End Set
        End Property

        Public Property VisaRequired As Boolean
            Get
                Return CastToBoolean(GetProperty("VisaRequired"))
            End Get
            Set(ByVal value As Boolean)
                SetProperty("VisaRequired", value)
            End Set
        End Property

        Public Property AvailableStartDate As Date
            Get
                Return CastToDate(GetProperty("AvailStartDate"))
            End Get
            Set(ByVal value As Date)
                SetProperty("AvailStartDate", value)
            End Set
        End Property

        Public Property AvailableEndDate As Date
            Get
                Return CastToDate(GetProperty("AvailEndDate"))
            End Get
            Set(ByVal value As Date)
                SetProperty("AvailEndDate", value)
            End Set
        End Property

        Public Property PrivateSessionRequired As Boolean
            Get
                Return CastToBoolean(GetProperty("PrivateSessionReq"))
            End Get
            Set(ByVal value As Boolean)
                SetProperty("PrivateSessionReq", value)
            End Set
        End Property

        Public Property CountryIssuesAddressed As String
            Get
                Return CastToString(GetProperty("CountryIssuesAddressed"))
            End Get
            Set(ByVal value As String)
                SetProperty("CountryIssuesAddressed", value)
            End Set
        End Property

        Public Property RegistrationDate As Date
            Get
                Return CastToDate(GetProperty("RegistrationDate"))
            End Get
            Set(ByVal value As Date)
                SetProperty("RegistrationDate", value)
            End Set
        End Property

        Public Property ConfirmationOfAttendance As Boolean
            Get
                Return CastToBoolean(GetProperty("ConfirmationOfAttendance"))
            End Get
            Set(ByVal value As Boolean)
                SetProperty("ConfirmationOfAttendance", value)
            End Set
        End Property

        Public Property SpecialityArea As String
            Get
                Return CastToString(GetProperty("SpecialityArea"))
            End Get
            Set(ByVal value As String)
                SetProperty("SpecialityArea", value)
            End Set
        End Property

        Public Property GroupID As Integer
            Get
                Return CastToInteger(GetProperty("GroupID"))
            End Get
            Set(ByVal value As Integer)
                SetProperty("GroupID", value)
            End Set
        End Property

#End Region

#Region "Methods"

        ''' <summary>
        ''' Method to get the Event Attendee details.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetEventAttendeeDetailsForEvent(ByVal piEventID As Integer) As DataTable
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                Dim lsSQl As String = String.Empty
                Dim loDT As DataTable
                lsSQl = "EXEC CMT_GetEventAttendeesDetails " + piEventID.ToString()
                loDT = loConn.GetDataTable(lsSQl)
                Return loDT
            End Using
        End Function

        ''' <summary>
        ''' Method: ImportAttendees
        ''' </summary>
        ''' <param name="eventid"></param>
        ''' <param name="xmlstring"></param>        ''' 
        ''' <remarks>
        ''' Inserts a line keeping track of any changes made in RM
        ''' </remarks>
        Public Function ImportAttendees(ByVal eventid As Integer, ByVal xmlstring As String, ByRef outputMessgae As String) As DataTable
            Dim rowsnotinserted As DataSet = New DataSet()
            Dim failedrows As DataTable = New DataTable()
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pEventId", SqlDbType.Int)
                loCommand.Parameters.Add("@pXmlString", SqlDbType.NVarChar)
                loCommand.Parameters.Add("@Output", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output

                loCommand.Parameters("@pEventId").Value = eventid
                loCommand.Parameters("@pXmlString").Value = xmlstring

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_EventAttendeesUpload"

                'Execute the Query
                Dim da As SqlDataAdapter = New SqlDataAdapter(loCommand)
                da.Fill(rowsnotinserted)
                If (rowsnotinserted.Tables.Count > 0) Then failedrows = rowsnotinserted.Tables(0)

                outputMessgae = CStr(loCommand.Parameters("@Output").Value)

                loConn.Connection.Close()
                Return failedrows
            End Using
        End Function

        ''' <summary>
        ''' Method: AddAttendees
        ''' </summary>
        ''' <param name="eventid"></param>
        ''' <param name="xmlstring"></param>        ''' 
        ''' <remarks>
        ''' Inserts a line keeping track of any changes made in RM
        ''' </remarks>
        Public Function AddAttendee(ByVal eventid As Integer, ByVal firstname As String, ByVal lastname As String, ByVal salutation As String,
                        ByVal prefformadd As String, ByVal emailadd As String, ByVal office As String, ByVal city As String, ByVal directdial As String,
                        ByVal mobnum As String, ByVal assitname As String, ByVal assistemail As String, ByVal assistnum As String, ByVal visareq As Boolean,
                        ByVal availstartdate As DateTime, ByVal availenddate As DateTime, ByVal complimentaryprivateeq As Boolean, ByVal countryissues As String,
                        ByVal registrationdate As DateTime, ByVal confirmation As Boolean, ByVal specialtyarea As String, ByVal countryrepresents As String,
                        ByVal isClient As Boolean, ByVal feedbackRequired As Boolean, ByVal isHost As Boolean) As String
            Dim message As String
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pEventID", SqlDbType.Int).Value = eventid
                loCommand.Parameters.Add("@pFirstName", SqlDbType.NVarChar, 100).Value = firstname
                loCommand.Parameters.Add("@pLastName", SqlDbType.NVarChar, 100).Value = lastname
                loCommand.Parameters.Add("@pSalutation", SqlDbType.NChar, 10).Value = salutation
                loCommand.Parameters.Add("@pEmailAddress", SqlDbType.NVarChar, 100).Value = emailadd
                loCommand.Parameters.Add("@pVisaRequired", SqlDbType.Bit).Value = visareq
                loCommand.Parameters.Add("@pConfirmationOfAttendance", SqlDbType.Bit).Value = confirmation
                loCommand.Parameters.Add("@pIsClient", SqlDbType.Bit).Value = isClient
                loCommand.Parameters.Add("@pFeedbackRequired", SqlDbType.Bit).Value = feedbackRequired
                loCommand.Parameters.Add("@pISHost", SqlDbType.Bit).Value = isHost

                If Not String.IsNullOrEmpty(prefformadd) Then
                    loCommand.Parameters.Add("@pPrefFormOfAddress", SqlDbType.NVarChar, 500).Value = prefformadd
                End If
                If Not String.IsNullOrEmpty(office) Then
                    loCommand.Parameters.Add("@pOffice", SqlDbType.NVarChar, 100).Value = office
                End If
                If Not String.IsNullOrEmpty(city) Then
                    loCommand.Parameters.Add("@pCity", SqlDbType.NVarChar, 100).Value = city
                End If
                If Not String.IsNullOrEmpty(mobnum) Then
                    loCommand.Parameters.Add("@pMobileNumber", SqlDbType.NVarChar, 50).Value = mobnum
                End If
                If Not String.IsNullOrEmpty(directdial) Then
                    loCommand.Parameters.Add("@pDirectDial", SqlDbType.NVarChar, 50).Value = directdial
                End If
                If Not String.IsNullOrEmpty(assitname) Then
                    loCommand.Parameters.Add("@pAssistantName", SqlDbType.NVarChar, 100).Value = assitname
                End If
                If Not String.IsNullOrEmpty(assistnum) Then
                    loCommand.Parameters.Add("@pAssistantNumber", SqlDbType.NVarChar, 50).Value = assistnum
                End If
                If Not String.IsNullOrEmpty(assistemail) Then
                    loCommand.Parameters.Add("@pAssistantEmail", SqlDbType.NVarChar, 100).Value = assistemail
                End If

                If Not availstartdate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pAvailStartDate", SqlDbType.DateTime).Value = availstartdate
                End If
                If Not availenddate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pAvailEndDate", SqlDbType.DateTime).Value = availenddate
                End If
                If Not registrationdate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pRegistrationDate", SqlDbType.DateTime).Value = registrationdate
                End If
                If Not String.IsNullOrEmpty(specialtyarea) Then
                    loCommand.Parameters.Add("@pSpecialtyArea", SqlDbType.NVarChar, 50).Value = specialtyarea
                End If
                If Not String.IsNullOrEmpty(countryrepresents) Then
                    loCommand.Parameters.Add("@pCountryRepresent", SqlDbType.NVarChar, 3000).Value = countryrepresents
                End If

                If isClient Then
                    loCommand.Parameters.Add("@pComplimentaryPrivateSessionReq", SqlDbType.Bit).Value = complimentaryprivateeq
                    loCommand.Parameters.Add("@pCountryIssuesAddressed", SqlDbType.NVarChar, 255).Value = countryissues
                End If

                Dim loParamInstance As SqlParameter = New SqlParameter("@Output", SqlDbType.NVarChar, 100)
                loParamInstance.Direction = ParameterDirection.Output
                loCommand.Parameters.Add(loParamInstance)

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_EventAttendeeAdd"

                'Execute the Query
                loCommand.ExecuteNonQuery()
                message = loCommand.Parameters("@Output").Value.ToString()
                loConn.Connection.Close()
                Return message
            End Using
        End Function

        Public Function ModifyAttendee(ByVal eventattendeeid As Integer, ByVal firstname As String, ByVal lastname As String, ByVal salutation As String, ByVal prefformadd As String,
                        ByVal emailadd As String, ByVal office As String, ByVal city As String, ByVal directdial As String, ByVal mobnum As String, ByVal assitname As String,
                        ByVal assistemail As String, ByVal assistnum As String, ByVal visareq As Boolean, ByVal availstartdate As DateTime, ByVal availenddate As DateTime,
                        ByVal complimentaryprivateeq As Boolean, ByVal countryissues As String, ByVal registrationdate As DateTime, ByVal confirmation As Boolean,
                        ByVal specialtyarea As String, ByVal countryrepresents As String, ByVal isClient As Boolean, ByVal feedbackRequired As Boolean, ByVal isHost As Boolean) As Integer
            Dim message As Integer
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pEventAttendeeId", SqlDbType.Int).Value = eventattendeeid
                loCommand.Parameters.Add("@pFirstName", SqlDbType.NVarChar, 100).Value = firstname
                loCommand.Parameters.Add("@pLastName", SqlDbType.NVarChar, 100).Value = lastname
                loCommand.Parameters.Add("@pSalutation", SqlDbType.NChar, 10).Value = salutation
                loCommand.Parameters.Add("@pEmailAddress", SqlDbType.NVarChar, 100).Value = emailadd

                loCommand.Parameters.Add("@pVisaRequired", SqlDbType.Bit).Value = visareq
                loCommand.Parameters.Add("@pConfirmationOfAttendance", SqlDbType.Bit).Value = confirmation
                loCommand.Parameters.Add("@pIsClient", SqlDbType.Bit).Value = isClient
                loCommand.Parameters.Add("@pFeedbackRequired", SqlDbType.Bit).Value = feedbackRequired
                loCommand.Parameters.Add("@pISHost", SqlDbType.Bit).Value = isHost

                If Not String.IsNullOrEmpty(prefformadd) Then
                    loCommand.Parameters.Add("@pPrefFormOfAddress", SqlDbType.NVarChar, 500).Value = prefformadd
                End If
                If Not String.IsNullOrEmpty(office) Then
                    loCommand.Parameters.Add("@pOffice", SqlDbType.NVarChar, 100).Value = office
                End If
                If Not String.IsNullOrEmpty(city) Then
                    loCommand.Parameters.Add("@pCity", SqlDbType.NVarChar, 100).Value = city
                End If
                If Not String.IsNullOrEmpty(mobnum) Then
                    loCommand.Parameters.Add("@pMobileNumber", SqlDbType.NVarChar, 50).Value = mobnum
                End If
                If Not String.IsNullOrEmpty(directdial) Then
                    loCommand.Parameters.Add("@pDirectDial", SqlDbType.NVarChar, 50).Value = directdial
                End If
                If Not String.IsNullOrEmpty(assitname) Then
                    loCommand.Parameters.Add("@pAssistantName", SqlDbType.NVarChar, 100).Value = assitname
                End If
                If Not String.IsNullOrEmpty(assistnum) Then
                    loCommand.Parameters.Add("@pAssistantNumber", SqlDbType.NVarChar, 50).Value = assistnum
                End If
                If Not String.IsNullOrEmpty(assistemail) Then
                    loCommand.Parameters.Add("@pAssistantEmail", SqlDbType.NVarChar, 100).Value = assistemail
                End If

                If Not availstartdate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pAvailStartDate", SqlDbType.DateTime).Value = availstartdate
                End If
                If Not availenddate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pAvailEndDate", SqlDbType.DateTime).Value = availenddate
                End If
                If Not registrationdate.Equals(DateTime.MinValue) Then
                    loCommand.Parameters.Add("@pRegistrationDate", SqlDbType.DateTime).Value = registrationdate
                End If
                If Not String.IsNullOrEmpty(specialtyarea) Then
                    loCommand.Parameters.Add("@pSpecialtyArea", SqlDbType.NVarChar, 50).Value = specialtyarea
                End If
                If Not String.IsNullOrEmpty(countryrepresents) Then
                    loCommand.Parameters.Add("@pCountryRepresent", SqlDbType.NVarChar, 3000).Value = countryrepresents
                End If

                If isClient Then
                    loCommand.Parameters.Add("@pComplimentaryPrivateSessionReq", SqlDbType.Bit).Value = complimentaryprivateeq
                    loCommand.Parameters.Add("@pCountryIssuesAddressed", SqlDbType.NVarChar, 255).Value = countryissues
                End If

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_EventAttendeeUpdate"

                'Execute the Query
                message = loCommand.ExecuteNonQuery()
                loConn.Connection.Close()
                Return message
            End Using
        End Function

        ''' <summary>
        ''' Method: GetEventAttendee
        ''' </summary>
        ''' <param name="firstname"></param>
        ''' <param name="lastname"></param>        ''' 
        ''' <remarks>
        ''' Inserts a line keeping track of any changes made in RM
        ''' </remarks>
        Public Function GetEventAttendee(ByVal firstname As String, ByVal lastname As String, ByVal eventid As Integer) As DataTable
            Dim eventattendees As DataSet = New DataSet()
            Dim attendees As DataTable = New DataTable()
            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)

                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                loCommand.Parameters.Add("@pFirstName", SqlDbType.NVarChar, 100)
                loCommand.Parameters.Add("@pLastName", SqlDbType.NVarChar, 100)
                loCommand.Parameters.Add("@pEventId", SqlDbType.Int)

                loCommand.Parameters("@pFirstName").Value = firstname
                loCommand.Parameters("@pLastName").Value = lastname
                loCommand.Parameters("@pEventId").Value = eventid

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_EventAttendeeSearch"

                'Execute the Query
                Dim da As SqlDataAdapter = New SqlDataAdapter(loCommand)
                da.Fill(eventattendees)
                If (eventattendees.Tables.Count > 0) Then attendees = eventattendees.Tables(0)
                loConn.Connection.Close()
                Return attendees
            End Using
        End Function

        ''' <summary>
        ''' Check if an attendee assigned to any meeting
        ''' </summary>
        ''' <param name="attendeeId"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function IsAttendeeAssignedToMeeting(ByVal attendeeId As Integer) As Boolean

            Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                Dim loCommand As SqlCommand = New SqlCommand
                'set the connection
                loCommand.Connection = loConn.Connection

                ' Set the parameters
                'loCommand.Parameters.Add("@pEventId", SqlDbType.Int).Value = eventId
                loCommand.Parameters.Add("@pEventAttendeeId", SqlDbType.Int)

                loCommand.Parameters("@pEventAttendeeId").Value = attendeeId

                loCommand.CommandType = Data.CommandType.StoredProcedure
                loCommand.CommandText = "CMT_IsEventAttendeeAssignedToMeeting"

                'Execute the Query
                Dim isAssigned As Boolean = Convert.ToBoolean(loCommand.ExecuteScalar())
                loConn.Connection.Close()

                Return isAssigned
            End Using
        End Function

        ''' <summary>
        ''' Send meeting details to the event attendee
        ''' </summary>
        ''' <param name="eventAttendeeId"></param>
        ''' <returns>mail send or not</returns>
        ''' <remarks></remarks>
        Public Function SendCalendarMail(ByVal eventAttendeeId As Integer) As Boolean
            Try
                Dim loDT As DataTable
                Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                    Dim lsSQl As String = String.Empty
                    lsSQl = "EXEC CMT_Mail_EventCalenderDetails " + eventAttendeeId.ToString()
                    loDT = loConn.GetDataTable(lsSQl)
                End Using

                Return SendMail(loDT)
            Catch ex As Exception
                Return False
            End Try
        End Function

        Private Function SendMail(ByVal dt As DataTable) As Boolean
            Try
                Dim loSetting As Settings = New Settings(SourceDataBase)
                Dim loEmail As Email = New Email(CInt(loSetting("CMT-MeetingNotificationEmailId").ToString))
                Dim Subject As String = loEmail.Subject
                Dim Body As String = loEmail.BodyText

                With loEmail
                    For Each dr As DataRow In dt.Rows
                        .Subject = Subject.Replace("~Subject", dr("Subject").ToString)

                        .BodyText = Body.Replace("~Name", dr("Name").ToString).Replace("~Body", dr("Body").ToString)

                        loEmail.SendEmail(dr("To").ToString)
                    Next
                End With
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        ''' <summary>
        ''' Check if the attendee available start and end date and time values can edit such that it can cover already scheduled(if any) meting time slots.
        ''' </summary>
        ''' <param name="eventAttendeeId"></param>
        ''' <param name="availableStartDate"></param>
        ''' <param name="availableEndDate"></param>
        ''' <returns>bool</returns>
        ''' <remarks></remarks>
        Public Function CanEditAttendeeAvilability(ByVal eventAttendeeId As Integer, ByVal availableStartDate As String, ByVal availableEndDate As String) As String
            Dim canEdit As String
            Using loConn As Connection = New Connection(SourceDataBase)
                Dim lsSQl As String = String.Empty
                lsSQl = "EXEC CMT_ChktheAvailablityforReShedule '" + availableStartDate + "', '" + availableEndDate + "'," + eventAttendeeId.ToString()
                canEdit = Convert.ToString(loConn.ExecuteScalar(lsSQl))
            End Using
            Return canEdit
        End Function

        ''' <summary>
        '''  Send Own Client/client particiapnt registration mail
        ''' </summary>
        ''' <param name="eventId"></param>
        ''' <param name="isClient"></param>
        ''' <returns>0: exception; 1: success; 2: already sent/no participants</returns>
        ''' <remarks></remarks>
        Public Function SendRegistrationMail(ByVal eventId As Integer, ByVal isClient As Integer) As DataTable
            Try
                Dim loDTMail As DataTable
                Dim loDTStatus As DataTable
                Dim loDS As DataSet
                Using loConn As Connection = New Connection(Connection.SourceDataBase.CMT)
                    Dim lsSQl As String = String.Empty
                    lsSQl = "EXEC CMT_Mail_EventAttendeeRegistrationReq " + eventId.ToString() + "," + isClient.ToString()

                    loDS = loConn.GetDataSet(lsSQl)
                    loDTMail = loDS.Tables(0) 'MeetingMail data
                    loDTStatus = loDS.Tables(1) ' status data
                End Using

                If loDTMail.Rows.Count > 0 Then
                    SendMail(loDTMail)
                End If

                Return loDTStatus
            Catch ex As Exception
                Throw ex
            End Try
        End Function
#End Region

    End Class

End Namespace